import sqlite3 from 'sqlite3'
import { open } from 'sqlite'
import path from 'path'

export const openDb = async () => {
  const db = await open({
    filename: path.join(__dirname, '..', 'students.db'),
    driver: sqlite3.Database
  })

  await db.exec(`
    CREATE TABLE IF NOT EXISTS students (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      studentId TEXT,
      name TEXT,
      attempts INTEGER DEFAULT 0,
      accuracy INTEGER DEFAULT 0,
      points INTEGER DEFAULT 0
    )
  `)

  return db
}

export const insertStudent = async (studentId: string, name: string, attempts: number, accuracy: number, points: number) => {
  const db = await openDb()
  await db.run(`INSERT INTO students (studentId, name, attempts, accuracy, points) VALUES (?, ?, ?, ?, ?)`, studentId, name, attempts, accuracy, points)
  await db.close()
}
export const getAllStudents = async () => {
  const db = await openDb()
  const students = await db.all<{ id: number; studentId: string; name: string; attempts: number; accuracy: number; points: number }[]>(`
    SELECT * FROM students
  `)
  await db.close()
  return students
}
